﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;

namespace FYPHP
{
    public partial class AdsCronJob : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["fyphp"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)
        {
            SqlCommand cmdSelect, cmdCron;
            SqlDataReader dtrSelect;
            conn.Open();

            cmdSelect = new SqlCommand("SELECT * FROM Advertisements WHERE (a_status='pending' OR a_status='published')", conn);
            dtrSelect = cmdSelect.ExecuteReader();
            List<int> aid = new List<int>();

            if (dtrSelect.HasRows)
            {
                while (dtrSelect.Read())
                {
                    DateTime now = DateTime.Now;
                    if ((Convert.ToDateTime(dtrSelect["a_date"]).AddDays(Convert.ToInt32(dtrSelect["a_duration"]))) < now)
                    {
                        aid.Add(Convert.ToInt32(dtrSelect["a_id"]));
                    }
                }
            }
            dtrSelect.Close();

            for (int i = 0; i < aid.Count; i++)
            {
                cmdCron = new SqlCommand("UPDATE Advertisements SET a_status='removed' WHERE a_id=@aid", conn);
                cmdCron.Parameters.AddWithValue("@aid", aid[i]);
                cmdCron.ExecuteNonQuery();
            }



            cmdSelect = new SqlCommand("SELECT v.v_id, MAX(endDate) AS edate FROM Virtual_Shop v, Shop_Charges sc WHERE v.v_id=sc.v_id AND v_status='Activate' GROUP BY v.v_id", conn);
            dtrSelect = cmdSelect.ExecuteReader();
            List<int> vid = new List<int>();

            if (dtrSelect.HasRows)
            {
                while (dtrSelect.Read())
                {
                    DateTime now = DateTime.Now;
                    if (Convert.ToDateTime(dtrSelect["eDate"]) < now)
                    {
                        vid.Add(Convert.ToInt32(dtrSelect["v_id"]));
                    }
                }
            }
            dtrSelect.Close();

            for (int i = 0; i < vid.Count; i++)
            {
                cmdCron = new SqlCommand("UPDATE Virtual_Shop SET v_status='Deactivate' WHERE v_id=@vid", conn);
                cmdCron.Parameters.AddWithValue("@vid", vid[i]);
                cmdCron.ExecuteNonQuery();
            }

            conn.Close();
            MessageBox("Cron successfully!\\nTotal Advertisements Cron: " + aid.Count + "\\nTotal Virtual Shop Cron: " + vid.Count);
            String url = Request.UrlReferrer.ToString();
            Redirect(url);
        }

        private void MessageBox(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language='javascript'>" + Environment.NewLine + "window.alert('" + msg + "')</script>";
            Page.Controls.Add(lbl);
        }

        private void Redirect(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language=\"javascript\">window.location='" + msg + "';</script>";
            Page.Controls.Add(lbl);
        }
    }
}